package com.joysuch.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.joysuch.entity.*;
import com.joysuch.enums.HourTypeEnum;
import com.joysuch.enums.WorkHourEnum;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

public class ExcelHandle {
//    // 输出路径
//    static String outPath = "C:/ExcelHandle";
//
//    // 模板文件
//    static InputStream templeteFile = ExecelHandle.class.getResourceAsStream("/报表模板.xlsx");
//
//    static String path = "C:\\Users\\20255\\Desktop\\报表\\测试\\样例.xlsx";
////    static String path = "C:\\Users\\gwl\\Desktop\\temp\\报表\\A.xlsx";

    /**
     * 处理报表
     * @param path 需要处理的Excel路径
     * @param templeteFile 模板文件
     * @param outPath 输出路径
     */
    public static void handle(File path, InputStream templeteFile, String outPath) {
        Set<String> rdNos = getRDNo();
        Set<String> leader = getLeaderName();
        List<Salary> inputList1 = ExcelUtil.readExcel(path, Salary.class, "输入1 工资表", 1);
        List<TimesSheet> inputList2 = ExcelUtil.readExcel(path, TimesSheet.class, "输入2 工时表", 3);
        List<MidA> midListA = ExcelUtil.readExcel(path, MidA.class, "中间表-研发工时分配A", 1);
        // 建表
        JdbcH2Util.createTable(MidA.class);
        // 插入数据
        for (MidA midA : midListA) {
            JdbcH2Util.insert(midA, MidA.class);
//            JdbcUtil.insert(midA, MidA.class);
        }
        List<MidA> allotList = new ArrayList<>();
        for (Salary salary : inputList1) {
            List<MidA> personalData = new ArrayList<>();
            String deptOne = salary.getDeptOne();
            String deptTwo = salary.getDeptTwo();
            boolean isSpecial = false;
            // 特殊情况
            if ("商业应用软件BU".equals(deptOne) && "定制化项目部".equals(deptTwo)) {
                isSpecial = true;
            }
            String name = salary.getName();

            String hasRdSql = "SELECT count(*) data FROM mida WHERE NAME = '" + name + "' AND allotScreen not LIKE '%分配%'";
            List<TempData> countList = JdbcH2Util.query(TempData.class, hasRdSql);
            Long count = countList.get(0).getData();
            // 如果包含领导
            if (leader.contains(name) || count == 0L) {

                String getPreAllotSql = "SELECT * FROM mida WHERE NAME = '" + name + "' ORDER BY personalHour DESC";
                List<MidA> preAllotList = JdbcH2Util.query(MidA.class, getPreAllotSql);
                MidA midA1 = preAllotList.get(0);

                // 找到领导的部门
                String getProSql = "SELECT * FROM mida WHERE deptOne = '" + midA1.getDeptOne() + "' AND allotScreen LIKE 'RD%' ORDER BY personalHour";
                List<MidA> leaderProList = JdbcH2Util.query(MidA.class, getProSql);
                // 计算总数
                BigDecimal temp = midA1.getPersonalHour();
                List<MidA> allot = allot(leaderProList, midA1, temp);

                if (leader.contains(name)) {
                    MidA midA2 = preAllotList.get(1);
                    midA2.setHourType(HourTypeEnum.原始.getAllotType());
                    midA2.setAddFlag(HourTypeEnum.分配.getAllotType());
                    allotList.add(midA2);
                    personalData.add(midA2);
                }
                allotList.addAll(allot);
                personalData.addAll(allot);

            } else {
                if ("N".equals(name)) {
                    System.out.println("hello");
                }

                String getProByName = "SELECT * FROM mida WHERE `NAME` = '" + name + "' order by personalHour";
                List<MidA> proList = JdbcH2Util.query(MidA.class, getProByName);
                // 总共要分配的工时数量
                BigDecimal allotSum = BigDecimal.ZERO;
                BigDecimal allotSum2 = BigDecimal.ZERO;
                List<MidA> preAllotList = new ArrayList<>();
                List<MidA> willAllot = new ArrayList<>();
                List<MidA> willAllot2 = new ArrayList<>();
                for (MidA midA : proList) {
                    String rdNo = midA.getRdNo();
                    String rdNoHead = "";
                    if (Objects.nonNull(rdNo) && rdNo.length() >= 2) {
                        rdNoHead = rdNo.substring(0, 2);
                    }
                    if (midA.getAllotScreen() != null && midA.getAllotScreen().contains("其他-分配")) {
                        allotSum = allotSum.add(midA.getPersonalHour());
                        willAllot.add(midA);
//                    } else if (midA.getAllotScreen() != null && midA.getRdNo().contains("RD")) {
//                        midA.setHourType(HourTypeEnum.原始.getAllotType());
//                        preAllotList.add(midA);
                    } else if (midA.getAllotScreen() != null && midA.getAllotScreen().contains("比对-分配")) {
                        allotSum2 = allotSum2.add(midA.getPersonalHour());
                        willAllot2.add(midA);
                    } else if (isSpecial) {
                        if ("CD".equals(rdNoHead)) {
                            midA.setHourType(HourTypeEnum.原始.getAllotType());
                            midA.setAddFlag(HourTypeEnum.原始.getAllotType());
                            preAllotList.add(midA);
                        }
                    } else if (rdNos.contains(rdNoHead)) {
                        midA.setHourType(HourTypeEnum.原始.getAllotType());
                        midA.setAddFlag(HourTypeEnum.原始.getAllotType());
                        preAllotList.add(midA);
                    } else if (!Objects.equals(midA.getDeptOne(), midA.getBu())) {
                        // 一级部门和BU不一致，为转嫁
                        midA.setAllotFlag(WorkHourEnum.转嫁.getAllotType());
                        midA.setHourType(HourTypeEnum.原始.getAllotType());
                        midA.setAddFlag(HourTypeEnum.原始.getAllotType());
                        allotList.add(midA);
                        personalData.add(midA);
                    } else {
                        System.out.println("eee");
                    }
                }
                List<MidA> allot = allot(preAllotList, salary, allotSum, willAllot);
                allot.forEach(midA -> {midA.setAddFlag(HourTypeEnum.原始.getAllotType());});
                List<MidA> allot2 = allot(preAllotList, salary, allotSum2, willAllot2);
//                BigDecimal sum = getSum(allot);
//                List<Map<String, BigDecimal>> tempList = new ArrayList<>();
//                for (MidA midA : allot) {
//                    Map<String, BigDecimal> tempMap = new HashMap<>();
//                    tempMap.put(midA.getName(), midA.getPersonalHour());
//                    tempList.add(tempMap);
//                }
//                tempList.forEach(System.out::println);
                allotList.addAll(preAllotList);
                personalData.addAll(preAllotList);

                allotList.addAll(allot);
                allotList.addAll(allot2);
                personalData.addAll(allot);
                personalData.addAll(allot2);
            }
            // 计算详情
            calcAll(personalData, salary);
        }
        Map<String, BigDecimal> tempOut = new HashMap<>();
        for (MidA midA : allotList) {
            String name = midA.getName();
            if (tempOut.containsKey(name)) {
                BigDecimal tempHour = tempOut.get(name);
                tempOut.put(name, tempHour.add(midA.getPersonalHour()));
            } else {
                tempOut.put(name, midA.getPersonalHour());
            }
        }

        JdbcH2Util.createTable(MidA.class, "mida2");
        for (MidA midA : allotList) {
            midA.setId(null);
            JdbcH2Util.insert(midA, MidA.class, "mida2");
//            JdbcUtilTemp.insert(midA, MidA.class, "mida2");
        }

        String yyyyMMddHHmmss = DateUtils.format(new Date(), "yyyyMMdd_HHmmss");
//        List<MidA> midAS = JdbcH2Util.query(MidA.class, "select * from mida");

        // 查询输出一
        String selectOut1Sql = "SELECT org, deptOne, deptTwo, bu, rdNo, proName, hourType, sum(personalHour) personalHour, sum(proBonus) proBonus, sum(basicSalary) basicSalary, sum(checkDeduct) checkDeduct, sum(otherDeduct) otherDeduct, sum(currentIncome) currentIncome, sum(personalSocialSecurity) personalSocialSecurity, sum(personalProvideAged) personalProvideAged, sum(personalMedical) personalMedical, sum(personalUnemployment) personalUnemployment, sum(personalAccuFunds) personalAccuFunds, sum(companySocialSecurity) companySocialSecurity, sum(conpanyProvideAged) conpanyProvideAged, sum(conpanyMedical) conpanyMedical, sum(conpanyUnemployment) conpanyUnemployment, sum(companyInjury) companyInjury, sum(companyBear) companyBear, sum(companyHeat) companyHeat, sum(companyDisableSecurity) companyDisableSecurity, sum(lateFees) lateFees, sum(companyAccuFunds) companyAccuFunds, sum(bjAccuFunds) bjAccuFunds, sum(incomeCompany) incomeCompany, sum(corporatePart) corporatePart, sum(bonus) bonus, sum(otherCheckDeduction) otherCheckDeduction from mida2 group by org, deptOne, deptTwo, bu, rdNo, hourType order by org, deptOne, deptTwo, bu, rdNo, hourType";
        List<Out1> out1 = JdbcH2Util.query(Out1.class, selectOut1Sql);

        // 查询输出二
        String selectOut2Sql = "SELECT ROW_NUMBER() OVER (ORDER BY deptOne) AS serialNumber, deptOne, deptTwo, army, bu, rdNo, proName, sum(personalHour) personalHour, sum(personalHour) personalHour, (sum(basicSalary) - sum(checkDeduct) - sum(otherDeduct) + sum(corporatePart) + sum(bjAccuFunds)) salary, sum(proBonus) proBonus, sum(bonus) preYearEndBonus, '0.0' total from mida2 group by deptOne, deptTwo, army, bu, rdNo order by deptOne, deptTwo, army, bu, rdNo";
        List<Out2> out2 = JdbcH2Util.query(Out2.class, selectOut2Sql);
        out2.forEach(out -> out.setTotal(out.getPreYearEndBonus().add(out.getProBonus()).add(out.getSalary())));

        // 查询输出三
        String selectOut3Sql = "SELECT mid.rdNo, mid.bu, mid.proName, sum(mid.personalHour) hourTotal, ifnull(sum(mid1.personalHour), '0') nxHour, ifnull(sum(mid1.incomeCompany), '0') nxIncomeCompany, ifnull(sum(mid1.bonus), '0') nxYearEndBonus, ifnull(sum(mid2.personalHour), '0') hourCheckHigh, IFNULL(sum(mid2.incomeCompany),'0') allotIncomeCompany, IFNULL(sum(mid2.bonus),'0') allotYearEndBonus from mida2 mid left join mida2 mid1 ON mid1.id = mid.id AND mid1.addFlag = '原始' left join mida2 mid2 ON mid2.id = mid.id AND mid2.addFlag = '分配' group by mid.rdNo, mid.bu, mid.proName order by mid.rdNo, mid.bu, mid.proName";
        List<Out3> out3 = JdbcH2Util.query(Out3.class, selectOut3Sql);
        BigDecimal temp = new BigDecimal(0);
        out3.forEach(out -> out.setTotal(out.getNxIncomeCompany().add(out.getNxYearEndBonus()).add(out.getAllotIncomeCompany()).add(out.getAllotYearEndBonus())));

        // 查询输出四
        String selectOut4Sql = "SELECT bu, rdNo, proName, deptOne, deptTwo, sum(basicSalary) basicSalary, sum(proBonus) proBonus, sum(bonus) bonus, (sum(currentIncome) + sum(companySocialSecurity) + sum(companyAccuFunds)) incomeCSecurity, sum(corporatePart) corporatePart, sum(bjAccuFunds) accuFunds, sum(otherCheckDeduction) checkOtherDeduct FROM mida2 GROUP BY bu, rdNo, proName, deptOne, deptTwo ORDER BY bu, rdNo, proName, deptOne, deptTwo";
        List<Out4> out4 = JdbcH2Util.query(Out4.class, selectOut4Sql);
        out4.forEach(out -> out.setTotal(out.getBasicSalary().add(out.getProBonus()).add(out.getBonus()).add(out.getCorporatePart()).add(out.getAccuFunds()).subtract(out.getCheckOtherDeduct())));

        String excelName = "/" + yyyyMMddHHmmss + "报表处理.xlsx";
        String excelFileName = outPath + excelName;
        File outputFolder = new File(outPath);
        if (!outputFolder.exists()) {
            outputFolder.mkdirs();// 如果文件夹不存在，则创建
        }
        try (ExcelWriter excelWriter = EasyExcel.write(excelFileName).withTemplate(templeteFile).build()) {
            WriteSheet workSheet = EasyExcel.writerSheet().sheetNo(2).build();
            WriteSheet outSheet1 = EasyExcel.writerSheet().sheetNo(4).build();
            WriteSheet outSheet2 = EasyExcel.writerSheet().sheetNo(5).build();
            WriteSheet outSheet3 = EasyExcel.writerSheet().sheetNo(6).build();
            WriteSheet outSheet4 = EasyExcel.writerSheet().sheetNo(7).build();

            excelWriter.fill(new FillWrapper("data", allotList), workSheet);
            excelWriter.fill(new FillWrapper("data", out1), outSheet1);
            excelWriter.fill(new FillWrapper("data", out2), outSheet2);
            excelWriter.fill(new FillWrapper("data", out3), outSheet3);
            excelWriter.fill(new FillWrapper("data", out4), outSheet4);
            excelWriter.finish();
        } finally {
            if (templeteFile != null) {
                try {
                    templeteFile.close(); // 关闭输入流
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        JdbcH2Util.closeConnection();


//        List<MidA> query = JdbcH2Util.query(MidA.class, "select * from mida2");
//        query.forEach(System.out::println);

//        BigDecimal tempSum = getTempSum(tempOut);
//        BigDecimal reduce = allotList.stream().map(MidA::getPersonalHour).reduce(BigDecimal.ZERO, BigDecimal::add);
//        System.out.println(reduce);


//        List<MidA> query = JdbcH2Util.query(MidA.class, "select * from mida");
//        query.forEach(System.out::println);
    }

    private static List<MidA> calcAll(List<MidA> list, Salary salary) {
        BigDecimal sum = getSum(list);

        BigDecimal proportionTemp = new BigDecimal(1);
        int size = list.size();
        BigDecimal proportion;
        BigDecimal temp = new BigDecimal(0);
        for (MidA midA : list) {
            if (size == 1) {
                proportion = proportionTemp;

            } else {
                proportion = midA.getPersonalHour().divide(sum, 12, RoundingMode.HALF_UP);
            }
            temp = temp.add(proportion);
            midA.setProportion(proportion);
            midA.calcAll(salary);

            proportionTemp = proportionTemp.subtract(proportion);
            size--;
        }

        return list;
    }

    private static List<MidA> allot(List<MidA> list, MidA midAS, BigDecimal allot) {
        BigDecimal sum = list.stream()
                .map(MidA::getPersonalHour)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        // 计算比例
        BigDecimal proportionTemp = new BigDecimal(1);
        BigDecimal allotTemp = allot;
        int size = list.size();
        for (MidA midA : list) {
            BigDecimal proportion;
            BigDecimal personalHour;
            if (size == 1) {
                proportion = proportionTemp;
                personalHour = allotTemp;

            } else {
                proportion = midA.getPersonalHour().divide(sum, 2, RoundingMode.HALF_UP);
                personalHour = allot.multiply(proportion);
            }
            midA.setProportion(proportion);
            midA.setPersonalHour(personalHour);

            midA.setProportion(proportion);
            midA.setName(midAS.getName());
            midA.setHourType(HourTypeEnum.分配.getAllotType());
            midA.setAddFlag(HourTypeEnum.分配.getAllotType());
            midA.calcAll(midAS);
            size--;
            allotTemp = allotTemp.subtract(midA.getPersonalHour());
            proportionTemp = proportionTemp.subtract(proportion);
        }
        return list;
    }

    private static List<MidA> allot(List<MidA> list, Salary salary, BigDecimal allot, List<MidA> willAllot) {
//        List<MidA> retList = new ArrayList<>(list);
        List<MidA> retList = new ArrayList<>();

        if (Objects.equals(allot, BigDecimal.ZERO)) {
            return retList;
        }
        if (list.isEmpty()) {
            return willAllot;
        }
        BigDecimal sum = list.stream()
                .map(MidA::getPersonalHour)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        // 计算比例
        BigDecimal proportionTemp = new BigDecimal(1);
        BigDecimal allotTemp = allot;
        int size = list.size();
        for (MidA midA : list) {
            try {
                MidA midATemp = (MidA) midA.clone();
                BigDecimal proportion;
                BigDecimal personalHour;
                if (size == 1) {
                    proportion = proportionTemp;
                    personalHour = allotTemp;
                } else {
                    proportion = midA.getPersonalHour().divide(sum, 2, RoundingMode.HALF_UP);
                    personalHour = allot.multiply(proportion);
                }
                midATemp.setProportion(proportion);
                midATemp.setPersonalHour(personalHour);

                midATemp.calcAll(salary);
                midATemp.setHourType(HourTypeEnum.分配.getAllotType());
                midATemp.setAddFlag(HourTypeEnum.分配.getAllotType());
                retList.add(midATemp);
                size--;
                allotTemp = allotTemp.subtract(personalHour);
                proportionTemp = proportionTemp.subtract(proportion);
            } catch (CloneNotSupportedException e) {
                throw new RuntimeException(e);
            }
        }
        return retList;
    }

    private static BigDecimal getTempSum(Map<String, BigDecimal> needAllotSource) {
        BigDecimal tempSum = new BigDecimal(0);
        for (Map.Entry<String, BigDecimal> entry : needAllotSource.entrySet()) {
            tempSum = tempSum.add(entry.getValue());
        }
        return tempSum;
    }

    private static BigDecimal getSum(List<MidA> list) {
        BigDecimal sum = list.stream()
                .map(MidA::getPersonalHour)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        return sum;
    }

    private static Set<String> getRDNo() {
        Set<String> set = new HashSet<>();
        set.add("RD");
        set.add("RDT");
        set.add("ZX");
        set.add("OR");
        set.add("MT");
        set.add("EH");
        return set;
    }

    private static Set<String> getLeaderName() {
        Set<String> leader = new HashSet<>();
        leader.add("E");
        leader.add("H");
        return leader;
    }
}


